package cn.swust.service.serviceimpl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import cn.swust.model.ProductBean;
import cn.swust.service.DetailProductService;
import cn.swust.service.ProductService;
import cn.swust.util.DBUtil;
import cn.swust.util.Pager;

public class ProductServiceImpl implements ProductService {

	/**
	 * 添加记录让不含有详情的产品外间都指向ID为1的DetailProduct表
	 */
	public int addProduct(ProductBean product) {
		
		DetailProductService detailSer = new DetailProductServiceImpl();
		int key = 1;
		String temp  = product.getDetailPro().getProState();
		if(temp == null || temp.equals("")){
			key = 1;
		}
		key = detailSer.addDetail(product.getDetailPro());
		int ret = 0;
		try{
		String insertSql = "insert into product(pro_caption,pro_picurl,pro_goldprice,pro_inventory" +
		",pro_donor,pro_picpath,pro_remark,pro_storetime,pro_keyword,detail_proid,pro_limit," +
		"class_id,pro_loveprice,pro_allpics) values ('" + product.getCaption() + "','" + product.getPicUrl() + "'," +
		product.getGoldPrice() + "," + product.getInventory() + ",'" + product.getDonor() + "','" +
		product.getPicPath() + "','" + product.getRemark() + "','" + product.getStoreTime() + "','" +
		product.getKeyword() + "'," + key + "," + product.getLimit()
		+ "," + product.getClassify() + "," + product.getLovePrice() + ",'" + product.getAllPics() + "')";
		System.out.println(insertSql);
		Connection conn = DBUtil.getConnection();
		Statement state = conn.createStatement();
		state.execute(insertSql, Statement.RETURN_GENERATED_KEYS);
		ResultSet rs = state.getGeneratedKeys();
		while(rs.next())
			ret = rs.getInt(1);
		} catch (SQLException e) {
			e.printStackTrace();
		} 
		if(ret == 0)
			if(key != 1)
				detailSer.delDetail(key);
		return ret;
	}

	public int delProduct(int id) {
		String delSql = "delete from product where pro_id = " + id;
		return DBUtil.updateSql(delSql);
	}

	public int modProduct(int id, ProductBean product) {
		String modSql = "update product set pro_caption = '" + product.getCaption() + "',pro_picurl = '"
		+ product.getPicUrl() + "',pro_goldprice = " + product.getGoldPrice() + ",pro_inventory = " +
		product.getInventory() + ",pro_donor = '" + product.getDonor() + "',pro_picpath = '" +
		product.getPicPath() + "',pro_remark = '" + product.getRemark() + "',pro_storetime = '" +
		product.getStoreTime() + "',pro_keyword = '" + product.getKeyword() + ",pro_limit = " +
		product.getLimit() + ",class_id = " + product.getClassify() + ",pro_loveprice = " +
		product.getLovePrice() + ",pro_allpics = '" + product.getAllPics() +
		"' where pro_id = " + id; 
		DetailProductService detail = new DetailProductServiceImpl();
		detail.modDetail(product.getDetailProId(), product.getDetailPro());
		return DBUtil.updateSql(modSql);
	}

	public ProductBean queryProById(int id) {
		String querySql = "select * from product where pro_id = " + id;
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		ProductBean pro = new ProductBean();
		try {
			if (rs.next()) {
				pro.setId(rs.getInt("pro_id"));
				pro.setCaption(rs.getString("pro_caption"));
				pro.setPicUrl(rs.getString("pro_picurl"));
				pro.setGoldPrice(rs.getFloat("pro_goldprice"));
				pro.setInventory(rs.getInt("pro_inventory"));
				pro.setDonor(rs.getString("pro_donor"));
				pro.setPicPath(rs.getString("pro_picpath"));
				pro.setRemark(rs.getString("pro_remark"));
				pro.setStoreTime(rs.getString("pro_storetime"));
				pro.setKeyword(rs.getString("pro_keyword"));
				pro.setDetailProId(rs.getInt("detail_proid"));
				pro.setLimit(rs.getInt("pro_limit"));
				pro.setClassify(rs.getInt("class_id"));
				pro.setLovePrice(rs.getFloat("pro_loveprice"));
				pro.setAllPics(rs.getString("pro_allpics"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return pro;
	}

	public ProductBean queryProMoreById(int id) {
		String querySql = "select * from product where pro_id = " + id;
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		ProductBean pro = new ProductBean();
		try {
			if (rs.next()) {
				pro.setId(rs.getInt("pro_id"));
				pro.setCaption(rs.getString("pro_caption"));
				pro.setPicUrl(rs.getString("pro_picurl"));
				pro.setGoldPrice(rs.getFloat("pro_goldprice"));
				pro.setInventory(rs.getInt("pro_inventory"));
				pro.setDonor(rs.getString("pro_donor"));
				pro.setPicPath(rs.getString("pro_picpath"));
				pro.setRemark(rs.getString("pro_remark"));
				pro.setStoreTime(rs.getString("pro_storetime"));
				pro.setKeyword(rs.getString("pro_keyword"));
				int detailId = rs.getInt("detail_proid");
				DetailProductService detail = new DetailProductServiceImpl();
				pro.setDetailProId(detailId);
				pro.setDetailPro(detail.queryById(detailId));
				pro.setLimit(rs.getInt("pro_limit"));
				pro.setClassify(rs.getInt("class_id"));
				pro.setLovePrice(rs.getFloat("pro_loveprice"));
				pro.setAllPics(rs.getString("pro_allpics"));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		return pro;
	}

	public ArrayList<ProductBean> queryAllPro(Pager pager, int sort) {
		String querySql = "select * from product limit " +
		(pager.getCurrentPage()*pager.getPageSize() - pager.getPageSize()) + "," + pager.getPageSize();
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		ArrayList<ProductBean> proList = new ArrayList<ProductBean>();
		try {
			while (rs.next()) {
				ProductBean pro = new ProductBean();
				pro.setId(rs.getInt("pro_id"));
				pro.setCaption(rs.getString("pro_caption"));
				pro.setPicUrl(rs.getString("pro_picurl"));
				pro.setGoldPrice(rs.getFloat("pro_goldprice"));
				pro.setInventory(rs.getInt("pro_inventory"));
				pro.setDonor(rs.getString("pro_donor"));
				pro.setPicPath(rs.getString("pro_picpath"));
				pro.setRemark(rs.getString("pro_remark"));
				pro.setStoreTime(rs.getString("pro_storetime"));
				pro.setKeyword(rs.getString("pro_keyword"));
				pro.setDetailProId(rs.getInt("detail_proid"));
				pro.setLimit(rs.getInt("pro_limit"));
				pro.setClassify(rs.getInt("class_id"));
				pro.setLovePrice(rs.getFloat("pro_loveprice"));
				pro.setAllPics(rs.getString("pro_allpics"));
				proList.add(pro);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return proList;
	}

	public ArrayList<ProductBean> queryAllProByClass(int type, Pager pager,  int sort) {
		String querySql = "select * from product where class_id = " + type + " limit " +
		(pager.getCurrentPage()*pager.getPageSize() - pager.getPageSize()) + "," + pager.getPageSize();
		if(type == -1)
			querySql = "select * from product limit " +
			(pager.getCurrentPage()*pager.getPageSize() - pager.getPageSize()) + "," + pager.getPageSize();
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		ArrayList<ProductBean> proList = new ArrayList<ProductBean>();
		try {
			while (rs.next()) {
				ProductBean pro = new ProductBean();
				pro.setId(rs.getInt("pro_id"));
				pro.setCaption(rs.getString("pro_caption"));
				pro.setPicUrl(rs.getString("pro_picurl"));
				pro.setGoldPrice(rs.getFloat("pro_goldprice"));
				pro.setInventory(rs.getInt("pro_inventory"));
				pro.setDonor(rs.getString("pro_donor"));
				pro.setPicPath(rs.getString("pro_picpath"));
				pro.setRemark(rs.getString("pro_remark"));
				pro.setStoreTime(rs.getString("pro_storetime"));
				pro.setKeyword(rs.getString("pro_keyword"));
				pro.setDetailProId(rs.getInt("detail_proid"));
				pro.setLimit(rs.getInt("pro_limit"));
				pro.setClassify(rs.getInt("class_id"));
				pro.setLovePrice(rs.getFloat("pro_loveprice"));
				pro.setAllPics(rs.getString("pro_allpics"));
				proList.add(pro);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return proList;
	}

	public ArrayList<ProductBean> queryPartProByWord(String keyWord, int sort) {
		String querySql = "select * from product where pro_keyword like %" + keyWord + "%";
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		ArrayList<ProductBean> proList = new ArrayList<ProductBean>();
		try {
			while (rs.next()) {
				ProductBean pro = new ProductBean();
				pro.setId(rs.getInt("pro_id"));
				pro.setCaption(rs.getString("pro_caption"));
				pro.setPicUrl(rs.getString("pro_picurl"));
				pro.setGoldPrice(rs.getFloat("pro_goldprice"));
				pro.setInventory(rs.getInt("pro_inventory"));
				pro.setDonor(rs.getString("pro_donor"));
				pro.setPicPath(rs.getString("pro_picpath"));
				pro.setRemark(rs.getString("pro_remark"));
				pro.setStoreTime(rs.getString("pro_storetime"));
				pro.setKeyword(rs.getString("pro_keyword"));
				pro.setDetailProId(rs.getInt("detail_proid"));
				pro.setLimit(rs.getInt("pro_limit"));
				pro.setClassify(rs.getInt("class_id"));
				pro.setLovePrice(rs.getFloat("pro_loveprice"));
				pro.setAllPics(rs.getString("pro_allpics"));
				proList.add(pro);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return proList;
	}

	public ArrayList<ProductBean> queryLastProByTime(int count) {
		String querySql = "select * from product order by pro_storetime desc limit " + count;
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		ArrayList<ProductBean> proList = new ArrayList<ProductBean>();
		try {
			while (rs.next()) {
				ProductBean pro = new ProductBean();
				pro.setId(rs.getInt("pro_id"));
				pro.setCaption(rs.getString("pro_caption"));
				pro.setPicUrl(rs.getString("pro_picurl"));
				pro.setGoldPrice(rs.getFloat("pro_goldprice"));
				pro.setInventory(rs.getInt("pro_inventory"));
				pro.setDonor(rs.getString("pro_donor"));
				pro.setPicPath(rs.getString("pro_picpath"));
				pro.setRemark(rs.getString("pro_remark"));
				pro.setStoreTime(rs.getString("pro_storetime"));
				pro.setKeyword(rs.getString("pro_keyword"));
				pro.setDetailProId(rs.getInt("detail_proid"));
				pro.setLimit(rs.getInt("pro_limit"));
				pro.setClassify(rs.getInt("class_id"));
				pro.setLovePrice(rs.getFloat("pro_loveprice"));
				pro.setAllPics(rs.getString("pro_allpics"));
				proList.add(pro);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return proList;
	}

	public int getListNum(int type) {
		String insertSql = "select count(*) from product where class_id = " + type;
		if(type == 0)
			insertSql = "select count(*) from product";
		ResultSet rs = DBUtil.querySql(insertSql, DBUtil.creatStatement(DBUtil.getConnection()));
		int ret = 0;
		try {
			if (rs.next()) {
				ret = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ret;
	}

	public int getCountCondition(String partSql) {
		String querySql = "select count(*) from product "+ partSql;
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		int ret = 0;
		try {
			if (rs.next()) {
				ret = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ret;
	}

	public ArrayList<ProductBean> querySql(String partSql, Pager pager) {
		String querySql = "select * from product "+ partSql +" limit " +
		(pager.getCurrentPage()*pager.getPageSize() - pager.getPageSize()) + "," + pager.getPageSize();
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		ArrayList<ProductBean> proList = new ArrayList<ProductBean>();
		try {
			while (rs.next()) {
				ProductBean pro = new ProductBean();
				pro.setId(rs.getInt("pro_id"));
				pro.setCaption(rs.getString("pro_caption"));
				pro.setPicUrl(rs.getString("pro_picurl"));
				pro.setGoldPrice(rs.getFloat("pro_goldprice"));
				pro.setInventory(rs.getInt("pro_inventory"));
				pro.setDonor(rs.getString("pro_donor"));
				pro.setPicPath(rs.getString("pro_picpath"));
				pro.setRemark(rs.getString("pro_remark"));
				pro.setStoreTime(rs.getString("pro_storetime"));
				pro.setKeyword(rs.getString("pro_keyword"));
				pro.setDetailProId(rs.getInt("detail_proid"));
				pro.setLimit(rs.getInt("pro_limit"));
				pro.setClassify(rs.getInt("class_id"));
				pro.setLovePrice(rs.getFloat("pro_loveprice"));
				pro.setAllPics(rs.getString("pro_allpics"));
				proList.add(pro);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return proList;
	}

	public ArrayList<ProductBean> queryAllByCondtion(int pageSize, int nowPage,
			int type, String sort) {
		String querySql = "select * from product where class_id="+type +" order by pro_goldprice "+sort.trim()+" limit " +
		(nowPage*pageSize - pageSize)+ "," + pageSize;
		System.out.println(querySql);
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		ArrayList<ProductBean> proList = new ArrayList<ProductBean>();
		try {
			while (rs.next()) {
				ProductBean pro = new ProductBean();
				pro.setId(rs.getInt("pro_id"));
				pro.setCaption(rs.getString("pro_caption"));
				pro.setPicUrl(rs.getString("pro_picurl"));
				pro.setGoldPrice(rs.getFloat("pro_goldprice"));
				pro.setInventory(rs.getInt("pro_inventory"));
				pro.setDonor(rs.getString("pro_donor"));
				pro.setPicPath(rs.getString("pro_picpath"));
				pro.setRemark(rs.getString("pro_remark"));
				pro.setStoreTime(rs.getString("pro_storetime"));
				pro.setKeyword(rs.getString("pro_keyword"));
				pro.setDetailProId(rs.getInt("detail_proid"));
				pro.setLimit(rs.getInt("pro_limit"));
				pro.setClassify(rs.getInt("class_id"));
				pro.setLovePrice(rs.getFloat("pro_loveprice"));
				pro.setAllPics(rs.getString("pro_allpics"));
				proList.add(pro);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return proList;
	}

	public int getCountByKey(String keyWord) {
		String querySql = "select count(*) from product where pro_keyword like '%"+ keyWord+"%'";
		System.out.println(querySql);
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		int ret = 0;
		try {
			if (rs.next()) {
				ret = rs.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return ret;
	}

	public ArrayList<ProductBean> queryPartProByWord(String keyWord, String sort,
			int pageSize, int nowPage) {
		String querySql = "select * from product where pro_keyword like '%"+ keyWord+"%' order by pro_goldprice "+sort.trim()+" limit " +
		(nowPage*pageSize - pageSize)+ "," + pageSize;
		System.out.println(querySql);
		ResultSet rs = DBUtil.querySql(querySql, DBUtil.creatStatement(DBUtil.getConnection()));
		ArrayList<ProductBean> proList = new ArrayList<ProductBean>();
		try {
			while (rs.next()) {
				ProductBean pro = new ProductBean();
				pro.setId(rs.getInt("pro_id"));
				pro.setCaption(rs.getString("pro_caption"));
				pro.setPicUrl(rs.getString("pro_picurl"));
				pro.setGoldPrice(rs.getFloat("pro_goldprice"));
				pro.setInventory(rs.getInt("pro_inventory"));
				pro.setDonor(rs.getString("pro_donor"));
				pro.setPicPath(rs.getString("pro_picpath"));
				pro.setRemark(rs.getString("pro_remark"));
				pro.setStoreTime(rs.getString("pro_storetime"));
				pro.setKeyword(rs.getString("pro_keyword"));
				pro.setDetailProId(rs.getInt("detail_proid"));
				pro.setLimit(rs.getInt("pro_limit"));
				pro.setClassify(rs.getInt("class_id"));
				pro.setLovePrice(rs.getFloat("pro_loveprice"));
				pro.setAllPics(rs.getString("pro_allpics"));
				proList.add(pro);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return proList;
	}

	public int opeProduct(int id, int num, int flag) {
		ProductBean pb = queryProById(id);
		int temp = pb.getInventory();
		if(flag == 0)
			temp = temp - num;
		else if(flag == 1)
			temp =  temp +  num;
		pb.setInventory(temp);
		return modProduct(id, pb);
	}
}
